package yzbx.personalwebsite.dao;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import yzbx.personalwebsite.entity.*;

import java.util.List;

/**
 * {@code @Author} zxm
 * {@code @Description} 通用的查询数据库的服务
 * {@code @Date} 2024/10/11/16:00
 * {@code @Version} 1.0
 */
@Component
public class CommonDao {

    private static final Logger LOGGER = LoggerFactory.getLogger(CommonDao.class);

    private final JdbcTemplate jdbcTemplate;

    public CommonDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public boolean pingMysql() {
        String sql = "SELECT 0;";
        try {
            jdbcTemplate.queryForObject(sql, Integer.class);
        } catch (Exception e) {
            return false;
        }
        return true;
    }

    /**
     * 通过用户id查询一个用户
     */
    public UserEntity selectUserById(String id) {
        String sql = "SELECT * FROM webserver.user where id = ?";
        List<UserEntity> users = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(UserEntity.class), id);
        if (!users.isEmpty()) {
            return users.get(0);
        }
        return null;
    }

    /**
     * 新增一个用户
     */
    public void insertUser(UserEntity user) {
        String sql = "INSERT INTO webserver.user (id, name, password,level,tx) VALUES (?, ?, ?, ?, ?)";
        jdbcTemplate.update(sql, user.getId(), user.getName(), user.getPassword(), user.getLevel(), user.getTx());
    }

    /**
     * 查询所有实用网站
     */
    public List<UsefWebsEntity> selectUsefWebs() {
        String sql = "SELECT * FROM webserver.usefWebs WHERE isdel='0' ORDER BY sort;";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(UsefWebsEntity.class));
    }

    /**
     * 新增一个实用网站
     */
    public void insertUsefWebs(UsefWebsEntity usefWebsEntity) {
        String sql = """
                INSERT INTO webserver.usefwebs (sort, title, description, url, img, group, isdel)
                SELECT temp.maxSort, ?, ?, ?, ?, ?, ?
                FROM (SELECT MAX(sort)+1 AS maxSort FROM webserver.usefwebs) AS temp;""";

        jdbcTemplate.update(sql,
                usefWebsEntity.getTitle(),
                usefWebsEntity.getDescription(),
                usefWebsEntity.getUrl(),
                usefWebsEntity.getImg(),
                usefWebsEntity.getGroup(),
                usefWebsEntity.getIsdel()
        );
    }

    /**
     * 更新一个实用网站
     */
    public void updateUsefWebs(UsefWebsEntity usefWebsEntity) {
        if (usefWebsEntity.getImg().isEmpty()) {
            String sql = """
                    UPDATE webserver.usefwebs SET title = ?, description = ?, url = ? , group = ?
                    WHERE sort = ?;""";

            jdbcTemplate.update(sql,
                    usefWebsEntity.getTitle(),
                    usefWebsEntity.getDescription(),
                    usefWebsEntity.getUrl(),
                    usefWebsEntity.getGroup(),
                    usefWebsEntity.getSort()
            );
        } else {
            String sql = """
                    UPDATE webserver.usefwebs SET title = ?, description = ?, url = ?, img = ?  WHERE sort = ?;""";

            jdbcTemplate.update(sql,
                    usefWebsEntity.getTitle(),
                    usefWebsEntity.getDescription(),
                    usefWebsEntity.getUrl(),
                    usefWebsEntity.getImg(),
                    usefWebsEntity.getSort()
            );
        }

    }

    /**
     * 更新实用网站的组
     */
    public void updateUsefWebsGroup(String group) {
        String sql = "UPDATE webserver.usefwebs SET `group` = '其他' WHERE `group`  = ?;";

        jdbcTemplate.update(sql, group);
    }

    /**
     * 逻辑删除一个实用网站
     */
    public void deleteUsefWebs(UsefWebsEntity usefWebsEntity) {
        String sql = "UPDATE webserver.usefwebs SET isdel = '1' WHERE sort = ?;";

        jdbcTemplate.update(sql,
                usefWebsEntity.getSort()
        );
    }

    /**
     * 查询未被删除的网站日志
     */
    public List<WebLogEntity> selectWebLog() {
        String sql = "SELECT * FROM webserver.weblog WHERE isdel='0' ORDER BY sort desc;";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(WebLogEntity.class));
    }

    /**
     * 新增一个网站日志
     */
    public void insertWebLog(WebLogEntity webLogEntity) {
        String sql = """
                INSERT INTO webserver.weblog (sort, title, description, date, isdel)
                SELECT temp.maxSort, ?, ?, ?, ?
                FROM (SELECT MAX(sort)+1 AS maxSort FROM webserver.weblog) AS temp;""";

        jdbcTemplate.update(sql,
                webLogEntity.getTitle(),
                webLogEntity.getDescription(),
                webLogEntity.getDate(),
                webLogEntity.getIsdel()
        );
    }


    /**
     * 逻辑删除一个网站日志
     */
    public void deleteWebLog(WebLogEntity webLogEntity) {
        String sql = "UPDATE webserver.weblog SET isdel = '1' WHERE sort = ?;";

        jdbcTemplate.update(sql,
                webLogEntity.getSort()
        );
    }

    /**
     * 查询未被删除文章的部分数据
     */
    public List<RecoIdeaEntity> selectRecoIdea(String author) {
        String sql = """
                SELECT
                  *
                FROM
                  webserver.recoIdea
                WHERE
                  isdel = '0'
                  AND (
                    permission = '0'
                    OR ( permission = '1' AND author = ? )
                  ) ORDER BY createTime desc;""";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(RecoIdeaEntity.class), author);
    }

    /**
     * 通过id查询未被删除且有权限观看的文章
     */
    public List<RecoIdeaEntity> selectRecoIdeaById(String id, String author) {
        String sql = """
                SELECT
                  *
                FROM
                  webserver.recoIdea
                WHERE
                  isdel = '0'
                  AND id = ?
                  AND (
                    permission = '0'
                    OR ( permission = '1' AND author = ? )
                  );""";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(RecoIdeaEntity.class), id, author);
    }


    /**
     * 新增一个文章
     */
    public void insertRecoIdea(RecoIdeaEntity recoIdea) {
        String sql = """
                INSERT INTO webserver.recoidea (id, author, authorId, title, textMini, text, date, isdel, permission, createTime, createUser, updateTime, updateUser)
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?);
                """;

        jdbcTemplate.update(sql,
                recoIdea.getId(),
                recoIdea.getAuthor(),
                recoIdea.getAuthorId(),
                recoIdea.getTitle(),
                recoIdea.getTextMini(),
                recoIdea.getText(),
                recoIdea.getDate(),
                recoIdea.getIsdel(),
                recoIdea.getPermission(),
                recoIdea.getCreateTime(),
                recoIdea.getCreateUser(),
                recoIdea.getUpdateTime(),
                recoIdea.getUpdateUser()
        );
    }

    /**
     * 逻辑删除一个文章
     */
    public void deleteRecoIdea(RecoIdeaEntity recoIdea) {
        String sql = "UPDATE webserver.recoIdea SET isdel = '1' WHERE id = ?";

        jdbcTemplate.update(sql,
                recoIdea.getId()
        );
    }

    /**
     * 查询未被删除实用网站组
     */
    public List<UsefWebsGroupEntity> selectUsefWebsGroup() {
        String sql = "SELECT name FROM webserver.usefwebsgroup WHERE isdel='0' order by createTime;";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(UsefWebsGroupEntity.class));
    }


    /**
     * 新增一个文章
     */
    public void insertUsefWebsGroup(UsefWebsGroupEntity usefWebsGroupEntity) {
        String sql = """
                INSERT INTO webserver.usefwebsgroup (name, isdel, createTime, createUser, updateTime, updateUser)
                VALUES (?, ?, ?, ?, ?, ?);
                """;

        jdbcTemplate.update(sql,
                usefWebsGroupEntity.getName(),
                usefWebsGroupEntity.getIsdel(),
                usefWebsGroupEntity.getCreateTime(),
                usefWebsGroupEntity.getCreateUser(),
                usefWebsGroupEntity.getUpdateTime(),
                usefWebsGroupEntity.getUpdateUser()
        );
    }

    /**
     * 逻辑删除一个文章
     */
    public void deleteUsefWebsGroup(UsefWebsGroupEntity usefWebsGroupEntity) {
        String sql = "UPDATE webserver.usefwebsgroup SET isdel = '1' WHERE name = ?";

        jdbcTemplate.update(sql,
                usefWebsGroupEntity.getName()
        );
    }

    /**
     * 查询实用网站组访问量
     */
    public List<UsefWebsUsesVisitRankEntity> selectUsefWebsUsesVisitRank() {
        String sql = "SELECT * FROM webserver.usefwebsusesvisitrank order by CAST(visit as SIGNED) desc;";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(UsefWebsUsesVisitRankEntity.class));
    }

    /**
     * 通过网站名查询实用网站组访问量
     */
    public List<UsefWebsUsesVisitRankEntity> selectUsefWebsUsesVisitRankbyTitle(String webTitle) {
        String sql = "SELECT * FROM webserver.usefwebsusesvisitrank WHERE webTitle =?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(UsefWebsUsesVisitRankEntity.class), webTitle);
    }

    /**
     * 新增网站访问量
     */
    public void insertUsefWebsUsesVisitRank(UsefWebsUsesVisitRankEntity usefWebsUsesVisitRankEntity) {
        String sql = """
                INSERT INTO webserver.usefwebsusesvisitrank (webTitle, visit)
                VALUES (?, '1');
                """;

        jdbcTemplate.update(sql,
                usefWebsUsesVisitRankEntity.getWebTitle()
        );
    }

    /**
     * 更新网站访问量
     */
    public void updateUsefWebsUsesVisitRank(UsefWebsUsesVisitRankEntity usefWebsUsesVisitRankEntity) {
        String sql = "UPDATE webserver.usefwebsusesvisitrank SET visit = visit + 1  WHERE webTitle = ?";

        jdbcTemplate.update(sql,
                usefWebsUsesVisitRankEntity.getWebTitle()
        );
    }

    /**
     * 查询聊天室
     */
    public List<TalkRoomEntity> selectTalkRoom() {
        String sql = "SELECT * FROM webserver.talkroom";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TalkRoomEntity.class));
    }

    /**
     * 查询聊天内容
     */
    public List<TalkRecordEntity> selectTalkRecord(String talkRoomId) {
        String sql = "SELECT * FROM webserver.talkrecord where talkRoomId = ? order by time desc LIMIT 50";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TalkRecordEntity.class), talkRoomId);
    }

    /**
     * 新增聊天内容
     */
    public void insertTalkRecord(TalkRecordEntity talkRecordEntity) {
        String sql = """
                INSERT INTO webserver.talkrecord (talkRoomId, authorId, authorName,authorTx, text, time)
                VALUES (?, ?, ?, ?, ?, ?);
                """;
        jdbcTemplate.update(sql,
                talkRecordEntity.getTalkRoomId(),
                talkRecordEntity.getAuthorId(),
                talkRecordEntity.getAuthorName(),
                talkRecordEntity.getAuthorTx(),
                talkRecordEntity.getText(),
                talkRecordEntity.getTime());
    }


}
